from flask import Flask, render_template, request, jsonify, redirect, url_for
import sqlite3
import os
from werkzeug.utils import secure_filename
import json
from datetime import datetime
from openpyxl import load_workbook
import xlrd

app = Flask(__name__)
app.config['UPLOAD_FOLDER'] = 'uploads'
app.config['MAX_CONTENT_LENGTH'] = 16 * 1024 * 1024  # 16MB max file size

# 确保上传目录存在
os.makedirs(app.config['UPLOAD_FOLDER'], exist_ok=True)

# 初始化数据库
def init_db():
    conn = sqlite3.connect('performance.db')
    cursor = conn.cursor()
    

    
    # 创建团队数据表
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS team_data (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            team_name TEXT NOT NULL,
            department TEXT,
            team_size INTEGER,
            avg_performance REAL,
            project_completion_rate REAL,
            upload_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    ''')
    
    # 创建项目工时统计表
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS project_worktime_stats (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            statistics_period TEXT NOT NULL,
            project_name TEXT NOT NULL,
            system_name TEXT NOT NULL,
            total_requirements INTEGER DEFAULT 0,
            total_worktime REAL DEFAULT 0,
            completed_requirements INTEGER DEFAULT 0,
            completed_worktime REAL DEFAULT 0,
            in_progress_requirements INTEGER DEFAULT 0,
            in_progress_worktime REAL DEFAULT 0,
            non_transactional_requirements INTEGER DEFAULT 0,
            transactional_requirements INTEGER DEFAULT 0,
            upload_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            UNIQUE(statistics_period, project_name, system_name)
        )
    ''')
    
    # 创建项目工时明细表
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS project_worktime_details (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            statistics_period TEXT NOT NULL,
            project_name TEXT NOT NULL,
            system_name TEXT NOT NULL,
            date_field TEXT,
            requirement_type TEXT,
            status TEXT,
            worktime REAL DEFAULT 0,
            work_content TEXT,
            person_name TEXT,
            person_role TEXT,
            upload_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    ''')
    
    # 创建成员工时数据表
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS member_worktime_data (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            statistics_period TEXT NOT NULL,
            saturation_rate REAL DEFAULT 0,
            used_worktime REAL DEFAULT 0,
            requirement_worktime REAL DEFAULT 0,
            ticket_worktime REAL DEFAULT 0,
            bug_worktime REAL DEFAULT 0,
            role TEXT,
            upload_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            UNIQUE(name, statistics_period)
        )
    ''')
    
    conn.commit()
    conn.close()

# 主页路由
@app.route('/')
def index():
    return render_template('index.html')

# 数据展示页面
@app.route('/dashboard')
def dashboard():
    return render_template('dashboard.html')

# 数据上传页面
@app.route('/upload')
def upload_page():
    return render_template('upload.html')

# 项目工时统计详情页面
@app.route('/project-detail')
def project_detail_page():
    period = request.args.get('period')
    project = request.args.get('project')
    return render_template('project_detail.html', period=period, project=project)

# 成员工时详情页面
@app.route('/member-worktime-detail')
def member_worktime_detail_page():
    name = request.args.get('name')
    period = request.args.get('period')
    return render_template('member_worktime_detail.html', name=name, period=period)



# API路由 - 获取成员工时数据
@app.route('/api/member-worktime-data')
def get_member_worktime_data():
    # 获取筛选参数
    period_filter = request.args.get('period')
    name_filter = request.args.get('name')
    
    conn = sqlite3.connect('performance.db')
    cursor = conn.cursor()
    
    # 构建查询条件
    where_conditions = []
    params = []
    
    if period_filter:
        where_conditions.append('statistics_period = ?')
        params.append(period_filter)
    
    if name_filter:
        where_conditions.append('name LIKE ?')
        params.append(f'%{name_filter}%')
    
    where_clause = ' AND '.join(where_conditions) if where_conditions else '1=1'
    
    query = f'''
        SELECT name, statistics_period, saturation_rate, used_worktime,
               requirement_worktime, ticket_worktime, bug_worktime, role, upload_time
        FROM member_worktime_data 
        WHERE {where_clause}
        ORDER BY statistics_period DESC, name
    '''
    
    cursor.execute(query, params)
    
    columns = ['name', 'statistics_period', 'saturation_rate', 'used_worktime',
               'requirement_worktime', 'ticket_worktime', 'bug_worktime', 'role', 'upload_time']
    data = [dict(zip(columns, row)) for row in cursor.fetchall()]
    
    conn.close()
    return jsonify(data)

# API路由 - 获取成员工时数据筛选选项
@app.route('/api/member-worktime-filter-options')
def get_member_worktime_filter_options():
    conn = sqlite3.connect('performance.db')
    cursor = conn.cursor()
    
    # 获取所有可用的统计周期
    cursor.execute('''
        SELECT DISTINCT statistics_period 
        FROM member_worktime_data 
        WHERE statistics_period IS NOT NULL AND statistics_period != ''
        ORDER BY statistics_period DESC
    ''')
    periods = [row[0] for row in cursor.fetchall()]
    
    # 获取所有可用的成员姓名
    cursor.execute('''
        SELECT DISTINCT name 
        FROM member_worktime_data 
        WHERE name IS NOT NULL AND name != ''
        ORDER BY name
    ''')
    names = [row[0] for row in cursor.fetchall()]
    
    conn.close()
    
    return jsonify({
        'periods': periods,
        'names': names
    })

# API路由 - 获取成员工时详情
@app.route('/api/member-worktime-detail')
def get_member_worktime_detail():
    name = request.args.get('name')
    statistics_period = request.args.get('period')
    
    if not name or not statistics_period:
        return jsonify({'error': '缺少必要参数'}), 400
    
    conn = sqlite3.connect('performance.db')
    cursor = conn.cursor()
    
    # 获取成员工时基本信息
    cursor.execute('''
        SELECT name, statistics_period, saturation_rate, used_worktime,
               requirement_worktime, ticket_worktime, bug_worktime, role, upload_time
        FROM member_worktime_data 
        WHERE name = ? AND statistics_period = ?
    ''', (name, statistics_period))
    
    member_info = cursor.fetchone()
    if not member_info:
        conn.close()
        return jsonify({'error': '未找到该成员的工时数据'}), 404
    
    # 获取该成员在项目工时明细表中的相关工作记录
    cursor.execute('''
        SELECT statistics_period, project_name, system_name, work_content, 
               worktime, requirement_type, status, person_role, upload_time
        FROM project_worktime_details 
        WHERE person_name = ? AND statistics_period = ?
        ORDER BY project_name, system_name, work_content
    ''', (name, statistics_period))
    
    work_details = []
    project_stats = {}
    system_stats = {}
    requirement_stats = {}
    
    for row in cursor.fetchall():
        project_name = row[1] or '未指定项目'
        system_name = row[2] or '未指定系统'
        work_content = row[3] or '未指定内容'
        worktime = row[4] or 0
        requirement_type = row[5] or '未指定类型'
        status = row[6] or '未指定状态'
        
        # 详细工作记录
        work_details.append({
            'statistics_period': row[0],
            'project_name': project_name,
            'system_name': system_name,
            'work_content': work_content,
            'worktime': worktime,
            'requirement_type': requirement_type,
            'status': status,
            'person_role': row[7] or '未指定角色',
            'upload_time': row[8]
        })
        
        # 按项目统计工时
        if project_name not in project_stats:
            project_stats[project_name] = {
                'total_worktime': 0,
                'requirement_count': 0,
                'systems': set(),
                'requirements': set()
            }
        project_stats[project_name]['total_worktime'] += worktime
        project_stats[project_name]['systems'].add(system_name)
        project_stats[project_name]['requirements'].add(work_content)
        
        # 按系统统计工时
        system_key = f"{project_name} - {system_name}"
        if system_key not in system_stats:
            system_stats[system_key] = {
                'project_name': project_name,
                'system_name': system_name,
                'total_worktime': 0,
                'requirement_count': 0,
                'requirements': set()
            }
        system_stats[system_key]['total_worktime'] += worktime
        system_stats[system_key]['requirements'].add(work_content)
        
        # 按需求统计工时
        requirement_key = f"{project_name} - {system_name} - {work_content}"
        if requirement_key not in requirement_stats:
            requirement_stats[requirement_key] = {
                'project_name': project_name,
                'system_name': system_name,
                'work_content': work_content,
                'total_worktime': 0,
                'requirement_type': requirement_type,
                'status': status
            }
        requirement_stats[requirement_key]['total_worktime'] += worktime
    
    # 转换统计数据为列表格式
    project_summary = []
    for project_name, stats in project_stats.items():
        project_summary.append({
            'project_name': project_name,
            'total_worktime': stats['total_worktime'],
            'system_count': len(stats['systems']),
            'requirement_count': len(stats['requirements']),
            'systems': list(stats['systems']),
            'requirements': list(stats['requirements'])
        })
    
    system_summary = []
    for system_key, stats in system_stats.items():
        system_summary.append({
            'project_name': stats['project_name'],
            'system_name': stats['system_name'],
            'total_worktime': stats['total_worktime'],
            'requirement_count': len(stats['requirements']),
            'requirements': list(stats['requirements'])
        })
    
    requirement_summary = []
    for requirement_key, stats in requirement_stats.items():
        requirement_summary.append({
            'project_name': stats['project_name'],
            'system_name': stats['system_name'],
            'work_content': stats['work_content'],
            'total_worktime': stats['total_worktime'],
            'requirement_type': stats['requirement_type'],
            'status': stats['status']
        })
    
    # 按工时降序排序
    project_summary.sort(key=lambda x: x['total_worktime'], reverse=True)
    system_summary.sort(key=lambda x: x['total_worktime'], reverse=True)
    requirement_summary.sort(key=lambda x: x['total_worktime'], reverse=True)
    
    conn.close()
    
    # 构建返回数据
    member_detail = {
        'name': member_info[0],
        'statistics_period': member_info[1],
        'saturation_rate': member_info[2],
        'used_worktime': member_info[3],
        'requirement_worktime': member_info[4],
        'ticket_worktime': member_info[5],
        'bug_worktime': member_info[6],
        'role': member_info[7],
        'upload_time': member_info[8],
        'work_details': work_details,
        'project_summary': project_summary,
        'system_summary': system_summary,
        'requirement_summary': requirement_summary,
        'total_project_worktime': sum(detail['worktime'] for detail in work_details),
        'project_count': len(project_stats),
        'system_count': len(system_stats),
        'requirement_count': len(requirement_stats)
    }
    
    return jsonify(member_detail)



# API路由 - 获取项目工时统计数据
@app.route('/api/project-worktime-stats')
def get_project_worktime_stats():
    # 获取筛选参数
    period_filter = request.args.get('period')
    project_filter = request.args.get('project')
    system_filter = request.args.get('system')
    
    conn = sqlite3.connect('performance.db')
    cursor = conn.cursor()
    
    # 构建查询条件
    where_conditions = []
    params = []
    
    if period_filter:
        where_conditions.append('statistics_period = ?')
        params.append(period_filter)
    
    if project_filter:
        where_conditions.append('project_name LIKE ?')
        params.append(f'%{project_filter}%')
        
    if system_filter:
        where_conditions.append('system_name LIKE ?')
        params.append(f'%{system_filter}%')
    
    where_clause = ' AND '.join(where_conditions) if where_conditions else '1=1'
    
    query = f'''
        SELECT statistics_period, project_name, system_name, 
               total_requirements, total_worktime,
               completed_requirements, completed_worktime,
               in_progress_requirements, in_progress_worktime,
               non_transactional_requirements, transactional_requirements,
               upload_time
        FROM project_worktime_stats 
        WHERE {where_clause}
        ORDER BY statistics_period DESC, project_name, system_name
    '''
    
    cursor.execute(query, params)
    
    columns = ['statistics_period', 'project_name', 'system_name', 
               'total_requirements', 'total_worktime',
               'completed_requirements', 'completed_worktime',
               'in_progress_requirements', 'in_progress_worktime',
               'non_transactional_requirements', 'transactional_requirements',
               'upload_time']
    data = [dict(zip(columns, row)) for row in cursor.fetchall()]
    
    conn.close()
    return jsonify(data)

# API路由 - 获取筛选选项数据
@app.route('/api/project-worktime-filter-options')
def get_project_worktime_filter_options():
    conn = sqlite3.connect('performance.db')
    cursor = conn.cursor()
    
    # 获取所有可用的统计周期
    cursor.execute('''
        SELECT DISTINCT statistics_period 
        FROM project_worktime_stats 
        WHERE statistics_period IS NOT NULL AND statistics_period != ''
        ORDER BY statistics_period DESC
    ''')
    periods = [row[0] for row in cursor.fetchall()]
    
    # 获取所有可用的项目名称
    cursor.execute('''
        SELECT DISTINCT project_name 
        FROM project_worktime_stats 
        WHERE project_name IS NOT NULL AND project_name != ''
        ORDER BY project_name
    ''')
    projects = [row[0] for row in cursor.fetchall()]
    
    # 获取所有可用的系统名称
    cursor.execute('''
        SELECT DISTINCT system_name 
        FROM project_worktime_stats 
        WHERE system_name IS NOT NULL AND system_name != ''
        ORDER BY system_name
    ''')
    systems = [row[0] for row in cursor.fetchall()]
    
    conn.close()
    
    return jsonify({
        'periods': periods,
        'projects': projects,
        'systems': systems
    })

# API路由 - 获取项目工时明细数据
@app.route('/api/project-worktime-details')
def get_project_worktime_details():
    statistics_period = request.args.get('period')
    project_name = request.args.get('project')
    system_name = request.args.get('system')
    
    conn = sqlite3.connect('performance.db')
    cursor = conn.cursor()
    
    # 构建查询条件
    where_conditions = []
    params = []
    
    if statistics_period:
        where_conditions.append('statistics_period = ?')
        params.append(statistics_period)
    
    if project_name:
        where_conditions.append('project_name = ?')
        params.append(project_name)
        
    if system_name:
        where_conditions.append('system_name = ?')
        params.append(system_name)
    
    where_clause = ' AND '.join(where_conditions) if where_conditions else '1=1'
    
    cursor.execute(f'''
        SELECT statistics_period, project_name, system_name, 
               date_field, requirement_type, status, worktime, upload_time
        FROM project_worktime_details 
        WHERE {where_clause}
        ORDER BY upload_time DESC
    ''', params)
    
    columns = ['statistics_period', 'project_name', 'system_name', 
               'date_field', 'requirement_type', 'status', 'worktime', 'upload_time']
    data = [dict(zip(columns, row)) for row in cursor.fetchall()]
    
    conn.close()
    return jsonify(data)

# API路由 - 获取项目详情数据
@app.route('/api/project-detail')
def get_project_detail():
    statistics_period = request.args.get('period')
    project_name = request.args.get('project')
    
    if not statistics_period:
        return jsonify({'error': '缺少统计周期参数'}), 400
    
    # 处理空项目名称的情况
    if project_name is None:
        return jsonify({'error': '缺少项目名称参数'}), 400
    
    conn = sqlite3.connect('performance.db')
    cursor = conn.cursor()
    
    # 获取项目基本信息和系统列表
    cursor.execute('''
        SELECT system_name, 
               total_requirements, total_worktime,
               completed_requirements, completed_worktime,
               in_progress_requirements, in_progress_worktime,
               non_transactional_requirements, transactional_requirements
        FROM project_worktime_stats 
        WHERE statistics_period = ? AND project_name = ?
        ORDER BY system_name
    ''', (statistics_period, project_name))
    
    systems_data = []
    total_project_requirements = 0
    total_project_worktime = 0
    total_project_roles = set()
    
    for row in cursor.fetchall():
        system_name, total_req, total_work, comp_req, comp_work, prog_req, prog_work, non_trans, trans = row
        systems_data.append({
            'system_name': system_name,
            'total_requirements': total_req,
            'total_worktime': total_work,
            'completed_requirements': comp_req,
            'completed_worktime': comp_work,
            'in_progress_requirements': prog_req,
            'in_progress_worktime': prog_work,
            'non_transactional_requirements': non_trans,
            'transactional_requirements': trans
        })
        total_project_requirements += total_req
        total_project_worktime += total_work
    
    # 获取每个系统的详细需求信息
    systems_details = {}
    for system_data in systems_data:
        system_name = system_data['system_name']
        
        # 获取该系统的需求详情
        cursor.execute('''
            SELECT work_content, person_name, person_role, worktime, requirement_type, status
            FROM project_worktime_details 
            WHERE statistics_period = ? AND project_name = ? AND system_name = ?
            ORDER BY work_content, person_name
        ''', (statistics_period, project_name, system_name))
        
        requirements = []
        requirement_names = set()
        system_roles = set()
        
        for detail_row in cursor.fetchall():
            work_content, person_name, person_role, worktime, req_type, status = detail_row
            
            if work_content:
                requirement_names.add(work_content)
            
            if person_role:
                system_roles.add(person_role)
                total_project_roles.add(person_role)
            
            requirements.append({
                'work_content': work_content or '未指定需求',
                'person_name': person_name or '未指定',
                'person_role': person_role or '未指定',
                'worktime': worktime,
                'requirement_type': req_type or '未指定',
                'status': status or '未指定'
            })
        
        systems_details[system_name] = {
            'requirements': requirements,
            'requirement_names': list(requirement_names),
            'requirement_count': len(requirement_names),
            'roles': list(system_roles),
            'role_count': len(system_roles)
        }
    
    # 获取项目涉及的所有角色统计
    cursor.execute('''
        SELECT person_role, COUNT(DISTINCT person_name) as person_count, SUM(worktime) as total_worktime
        FROM project_worktime_details 
        WHERE statistics_period = ? AND project_name = ? AND person_role IS NOT NULL AND person_role != ''
        GROUP BY person_role
        ORDER BY total_worktime DESC
    ''', (statistics_period, project_name))
    
    role_stats = []
    for row in cursor.fetchall():
        role, person_count, role_worktime = row
        role_stats.append({
            'role': role,
            'person_count': person_count,
            'worktime': role_worktime
        })
    
    conn.close()
    
    return jsonify({
        'project_info': {
            'statistics_period': statistics_period,
            'project_name': project_name,
            'total_systems': len(systems_data),
            'total_requirements': total_project_requirements,
            'total_worktime': total_project_worktime,
            'total_roles': len(total_project_roles),
            'role_stats': role_stats
        },
        'systems': systems_data,
        'systems_details': systems_details
    })

# API路由 - 调试上传数据结构
@app.route('/api/debug-upload', methods=['POST'])
def debug_upload():
    """调试接口：查看上传文件的数据结构"""
    if 'file' not in request.files:
        return jsonify({'error': '没有选择文件'}), 400
    
    file = request.files['file']
    
    if file.filename == '':
        return jsonify({'error': '没有选择文件'}), 400
    
    if not file.filename.lower().endswith(('.xlsx', '.xls')):
        return jsonify({'error': '请上传Excel文件'}), 400
    
    temp_filename = secure_filename(file.filename)
    temp_path = os.path.join(app.config['UPLOAD_FOLDER'], temp_filename)
    
    try:
        file.save(temp_path)
        
        headers = []
        sample_rows = []
        
        if file.filename.lower().endswith('.xlsx'):
            workbook = load_workbook(temp_path)
            worksheet = workbook.active
            
            # 获取表头
            for cell in worksheet[1]:
                headers.append(cell.value if cell.value else '')
            
            # 获取前5行数据作为样本
            row_count = 0
            for row in worksheet.iter_rows(min_row=2, values_only=True):
                if any(cell is not None for cell in row) and row_count < 5:
                    sample_rows.append(list(row))
                    row_count += 1
        
        elif file.filename.lower().endswith('.xls'):
            workbook = xlrd.open_workbook(temp_path)
            worksheet = workbook.sheet_by_index(0)
            
            if worksheet.nrows > 0:
                headers = [str(cell.value) if cell.value else '' for cell in worksheet.row(0)]
            
            for row_idx in range(1, min(6, worksheet.nrows)):
                row_data = []
                for col_idx in range(worksheet.ncols):
                    cell = worksheet.cell(row_idx, col_idx)
                    row_data.append(cell.value)
                sample_rows.append(row_data)
        
        # 清理临时文件
        os.remove(temp_path)
        
        return jsonify({
            'filename': file.filename,
            'headers': headers,
            'sample_rows': sample_rows,
            'total_headers': len(headers),
            'sample_count': len(sample_rows)
        })
        
    except Exception as e:
        try:
            os.remove(temp_path)
        except:
            pass
        return jsonify({'error': f'调试失败: {str(e)}'}), 500

# API路由 - 文件上传
@app.route('/api/upload', methods=['POST'])
def upload_file():
    if 'file' not in request.files:
        return jsonify({'error': '没有选择文件'}), 400
    
    file = request.files['file']
    upload_type = request.form.get('type')
    
    if file.filename == '':
        return jsonify({'error': '没有选择文件'}), 400
    
    if not file.filename.lower().endswith(('.xlsx', '.xls')):
        return jsonify({'error': '请上传Excel文件(.xlsx或.xls格式)'}), 400
    
    # 检查文件大小
    file.seek(0, 2)  # 移动到文件末尾
    file_size = file.tell()
    file.seek(0)  # 重置文件指针
    
    if file_size == 0:
        return jsonify({'error': '文件为空，请选择有效的Excel文件'}), 400
    
    if file_size > app.config['MAX_CONTENT_LENGTH']:
        return jsonify({'error': '文件过大，请选择小于16MB的文件'}), 400
    
    # 生成唯一的文件名并保存到uploads文件夹
    original_filename = secure_filename(file.filename)
    if not original_filename:
        original_filename = f"upload_{datetime.now().strftime('%Y%m%d_%H%M%S')}.xlsx"
    
    # 添加时间戳确保文件名唯一
    timestamp = datetime.now().strftime('%Y%m%d_%H%M%S')
    name, ext = os.path.splitext(original_filename)
    saved_filename = f"{name}_{timestamp}{ext}"
    
    saved_path = os.path.join(app.config['UPLOAD_FOLDER'], saved_filename)
    
    try:
        # 保存上传的文件到uploads文件夹
        file.save(saved_path)
        
        # 验证文件确实被保存
        if not os.path.exists(saved_path):
            return jsonify({'error': '文件保存失败，请重试'}), 500
        
        # 读取Excel文件，支持.xlsx和.xls格式
        try:
            headers = []
            data_rows = []
            
            if file.filename.lower().endswith('.xlsx'):
                # 使用openpyxl读取.xlsx文件
                try:
                    workbook = load_workbook(saved_path)
                    worksheet = workbook.active
                    
                    # 获取表头
                    for cell in worksheet[1]:
                        headers.append(cell.value if cell.value else '')
                    
                    # 读取数据行
                    for row in worksheet.iter_rows(min_row=2, values_only=True):
                        if any(cell is not None for cell in row):  # 跳过空行
                            data_rows.append(row)
                            
                except Exception as e:
                    if 'not a zip file' in str(e).lower():
                        return jsonify({'error': '文件格式错误：文件不是有效的Excel文件，请确保文件未损坏'}), 400
                    else:
                        return jsonify({'error': f'读取.xlsx文件失败: {str(e)}'}), 400
                        
            elif file.filename.lower().endswith('.xls'):
                # 使用xlrd读取.xls文件
                try:
                    workbook = xlrd.open_workbook(saved_path)
                    worksheet = workbook.sheet_by_index(0)
                    
                    # 获取表头
                    if worksheet.nrows > 0:
                        headers = [str(cell.value) if cell.value else '' for cell in worksheet.row(0)]
                    
                    # 读取数据行
                    for row_idx in range(1, worksheet.nrows):
                        row_data = []
                        for col_idx in range(worksheet.ncols):
                            cell = worksheet.cell(row_idx, col_idx)
                            row_data.append(cell.value)
                        
                        if any(cell is not None and str(cell).strip() for cell in row_data):  # 跳过空行
                            data_rows.append(tuple(row_data))
                            
                except Exception as e:
                    return jsonify({'error': f'读取.xls文件失败: {str(e)}'}), 400
            else:
                return jsonify({'error': '不支持的文件格式，请上传.xlsx或.xls文件'}), 400
            
            # 检查是否有有效数据
            if not headers or not data_rows:
                return jsonify({'error': '文件为空或没有有效数据'}), 400
                
        except Exception as e:
            error_msg = str(e)
            if 'no such file' in error_msg.lower():
                return jsonify({'error': '文件上传失败，请重试'}), 400
            else:
                return jsonify({'error': f'读取Excel文件失败: {error_msg}'}), 400
        
        conn = sqlite3.connect('performance.db')
        cursor = conn.cursor()
        
        if upload_type == 'member_worktime':
            # 处理成员工时数据
            count = process_member_worktime_data(cursor, headers, data_rows)
            
        elif upload_type == 'project_worktime':
            # 处理项目工时数据
            count = process_project_worktime_data(cursor, headers, data_rows)
            
        else:
            return jsonify({'error': '无效的上传类型'}), 400
        
        conn.commit()
        conn.close()
        
        return jsonify({
            'message': '上传成功，文件已保存到uploads文件夹', 
            'count': count,
            'saved_file': saved_filename
        })
        
    except Exception as e:
        return jsonify({'error': f'处理文件时出错: {str(e)}'}), 500

# 辅助函数：智能获取字段值
def get_field_value(row_dict, possible_names):
    """从行数据中智能匹配字段名并获取值"""
    for name in possible_names:
        if name in row_dict and row_dict[name] is not None:
            value = str(row_dict[name]).strip()
            if value:
                return value
    return None

# 辅助函数：获取数值类型字段
def get_numeric_value(row_dict, possible_names):
    """从行数据中智能匹配数值字段并获取值"""
    for name in possible_names:
        if name in row_dict and row_dict[name] is not None:
            try:
                value = float(row_dict[name])
                return value
            except (ValueError, TypeError):
                continue
    return None

# 处理项目工时数据
def process_project_worktime_data(cursor, headers, data_rows):
    """处理项目工时Excel数据并进行统计"""
    from collections import defaultdict
    
    # 不清空之前的数据，追加新数据
    
    # 统计数据结构
    stats = defaultdict(lambda: {
        'total_requirements': 0,
        'total_worktime': 0,
        'completed_requirements': 0,
        'completed_worktime': 0,
        'in_progress_requirements': 0,
        'in_progress_worktime': 0,
        'non_transactional_requirements': 0,
        'transactional_requirements': 0
    })
    
    # 用于需求去重和工时累加的字典
    requirement_worktime_map = defaultdict(lambda: {
        'total_worktime': 0,
        'requirement_type': '',
        'status': '',
        'date_field': '',
        'work_content': ''
    })
    
    count = 0
    statistics_period = datetime.now().strftime('%Y-%m')  # 默认统计周期
    
    # 第一步：需求去重和工时累加处理
    print("开始需求去重和工时累加处理...")
    for row_data in data_rows:
        if len(row_data) != len(headers):
            # 补齐或截断数据以匹配表头长度
            row_data = list(row_data)
            while len(row_data) < len(headers):
                row_data.append(None)
            row_data = row_data[:len(headers)]
        
        row_dict = dict(zip(headers, row_data))
        
        # 获取工作内容字段用于去重
        work_content = get_field_value(row_dict, [
            '工作内容', 'work_content', '内容', '任务内容', '需求内容', 
            '工作描述', '任务描述', '需求描述', '描述', '标题', '任务标题'
        ])
        
        # 获取人员信息字段
        person_name = get_field_value(row_dict, [
            '姓名', 'person_name', '人员姓名', '员工姓名', '处理人', '负责人', '开发人员'
        ])
        
        person_role = get_field_value(row_dict, [
            '角色', 'person_role', '职位', '岗位', '职务', '职责', '人员角色'
        ])
        
        # 精确匹配字段名 - 优先匹配Excel中的实际字段
        # 1. 统计周期字段
        statistics_period_field = get_field_value(row_dict, [
            '统计周期', 'statistics_period', '周期', '期间', '统计期间'
        ])
        
        # 2. 所属项目字段  
        project_name = get_field_value(row_dict, [
            '所属项目', 'project_name', '项目名称', '项目', 'project'
        ])
        
        # 3. 系统字段
        system_name = get_field_value(row_dict, [
            '系统', 'system', '系统名称', '系统字段', '子系统'
        ])
        
        # 4. 其他字段
        date_field = get_field_value(row_dict, [
            'date', '日期', '筛选日期', '时间', '创建时间', '更新时间'
        ])
        requirement_type = get_field_value(row_dict, [
            'requirement_type', '需求类型', '类型', '任务类型', 
            '工作类型', '业务类型', '功能类型'
        ])
        status = get_field_value(row_dict, [
            'status', '状态', '需求状态', '任务状态', '工作状态', 
            '进度状态', '完成状态', '执行状态'
        ])
        worktime = get_numeric_value(row_dict, [
            'worktime', '工时', '总工时', '总工时（人天）', '时长', '工作时间', 
            '耗时', '用时', '投入时间', '人时', '小时', '人天', '工时（人天）'
        ])
        
        # 只验证工时字段，项目名称和系统名称可以为空
        if not worktime:
            print(f"跳过无效数据行 - 缺少工时数据: {worktime}")
            continue
        
        # 如果项目名称或系统名称为空，设置为空字符串
        if not project_name:
            project_name = ""
        if not system_name:
            system_name = ""
        
        # 使用Excel中的统计周期字段，如果没有则尝试从日期字段提取
        if statistics_period_field:
            statistics_period = str(statistics_period_field).strip()
        elif date_field:
            try:
                if isinstance(date_field, datetime):
                    statistics_period = date_field.strftime('%Y-%m')
                else:
                    # 尝试解析日期字符串
                    date_str = str(date_field)
                    if len(date_str) >= 7:  # 至少包含年月
                        statistics_period = date_str[:7]  # 取前7位作为年月
            except:
                pass
        
        # 创建记录唯一标识：基于工作内容、项目、系统、人员的组合
        # 重要：每个人员在同一个需求上的工时应该分别记录，不应该累加
        if work_content:
            # 如果有工作内容，使用工作内容+项目+系统+人员作为记录标识
            requirement_key = f"{statistics_period}|{work_content}|{project_name}|{system_name}|{person_name or ''}|{requirement_type or ''}"
        else:
            # 如果没有工作内容，使用项目+系统+人员+需求类型+状态+日期作为记录标识
            requirement_key = f"{statistics_period}|{project_name}|{system_name}|{person_name or ''}|{requirement_type or ''}|{status or ''}|{date_field or ''}"
        
        # 累加工时到相同需求
        if requirement_key in requirement_worktime_map:
            print(f"发现相同需求，累加工时: {requirement_key} (+{worktime})")
            requirement_worktime_map[requirement_key]['total_worktime'] += worktime
        else:
            print(f"新需求: {requirement_key} (工时: {worktime})")
            requirement_worktime_map[requirement_key] = {
                'statistics_period': statistics_period,
                'project_name': project_name,
                'system_name': system_name,
                'date_field': date_field,
                'requirement_type': requirement_type,
                'status': status,
                'total_worktime': worktime,
                'work_content': work_content,
                'person_name': person_name,
                'person_role': person_role
            }
    
    print(f"需求去重完成：原始数据 {len(data_rows)} 条，去重后 {len(requirement_worktime_map)} 个需求")
    
    # 第二步：处理去重后的需求数据
    for requirement_key, req_data in requirement_worktime_map.items():
        # 保存明细数据（使用累加后的工时）
        cursor.execute('''
            INSERT INTO project_worktime_details 
            (statistics_period, project_name, system_name, date_field, requirement_type, status, worktime, work_content, person_name, person_role)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ''', (
            req_data['statistics_period'], 
            req_data['project_name'], 
            req_data['system_name'], 
            req_data['date_field'], 
            req_data['requirement_type'], 
            req_data['status'], 
            req_data['total_worktime'],  # 使用累加后的总工时
            req_data['work_content'],
            req_data['person_name'],
            req_data['person_role']
        ))
        
        # 统计键
        key = (req_data['statistics_period'], req_data['project_name'], req_data['system_name'])
        
        # 累加总需求数量和总工时（每个需求计为1个，但工时是累加的）
        stats[key]['total_requirements'] += 1
        stats[key]['total_worktime'] += req_data['total_worktime']
        
        # 需求类型统计
        if req_data['requirement_type'] and '事务' in str(req_data['requirement_type']):
            stats[key]['transactional_requirements'] += 1
        else:
            stats[key]['non_transactional_requirements'] += 1
        
        # 状态统计
        if req_data['status']:
            status_str = str(req_data['status']).lower()
            if '已完成' in status_str or '已上线' in status_str or '完成' in status_str:
                stats[key]['completed_requirements'] += 1
                stats[key]['completed_worktime'] += req_data['total_worktime']
            else:
                stats[key]['in_progress_requirements'] += 1
                stats[key]['in_progress_worktime'] += req_data['total_worktime']
        else:
            # 默认为在途
            stats[key]['in_progress_requirements'] += 1
            stats[key]['in_progress_worktime'] += req_data['total_worktime']
        
        count += 1
    
    # 保存统计数据 - 累加到现有数据而不是替换
    for (period, project, system), data in stats.items():
        # 首先检查是否已存在相同的统计记录
        cursor.execute('''
            SELECT total_requirements, total_worktime, completed_requirements, completed_worktime,
                   in_progress_requirements, in_progress_worktime, non_transactional_requirements, 
                   transactional_requirements
            FROM project_worktime_stats 
            WHERE statistics_period = ? AND project_name = ? AND system_name = ?
        ''', (period, project, system))
        
        existing = cursor.fetchone()
        
        if existing:
            # 如果存在，则累加数据
            cursor.execute('''
                UPDATE project_worktime_stats 
                SET total_requirements = total_requirements + ?,
                    total_worktime = total_worktime + ?,
                    completed_requirements = completed_requirements + ?,
                    completed_worktime = completed_worktime + ?,
                    in_progress_requirements = in_progress_requirements + ?,
                    in_progress_worktime = in_progress_worktime + ?,
                    non_transactional_requirements = non_transactional_requirements + ?,
                    transactional_requirements = transactional_requirements + ?,
                    upload_time = CURRENT_TIMESTAMP
                WHERE statistics_period = ? AND project_name = ? AND system_name = ?
            ''', (
                data['total_requirements'], data['total_worktime'],
                data['completed_requirements'], data['completed_worktime'],
                data['in_progress_requirements'], data['in_progress_worktime'],
                data['non_transactional_requirements'], data['transactional_requirements'],
                period, project, system
            ))
        else:
            # 如果不存在，则插入新记录
            cursor.execute('''
                INSERT INTO project_worktime_stats 
                (statistics_period, project_name, system_name, total_requirements, total_worktime,
                 completed_requirements, completed_worktime, in_progress_requirements, in_progress_worktime, 
                 non_transactional_requirements, transactional_requirements)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            ''', (
                period, project, system,
                data['total_requirements'], data['total_worktime'],
                data['completed_requirements'], data['completed_worktime'],
                data['in_progress_requirements'], data['in_progress_worktime'],
                data['non_transactional_requirements'], data['transactional_requirements']
            ))
    
    return count

# 处理成员工时数据
def process_member_worktime_data(cursor, headers, data_rows):
    """处理成员工时Excel数据"""
    count = 0
    
    for row_data in data_rows:
        if len(row_data) != len(headers):
            # 补齐或截断数据以匹配表头长度
            row_data = list(row_data)
            while len(row_data) < len(headers):
                row_data.append(None)
            row_data = row_data[:len(headers)]
        
        row_dict = dict(zip(headers, row_data))
        
        # 智能匹配字段名
        name = get_field_value(row_dict, [
            'name', '姓名', '员工姓名', '成员姓名', '名字', '人员姓名', 
            '员工', '成员', '人员', '工号', '编号'
        ])
        
        # 优先使用筛选日期作为统计周期
        statistics_period_field = get_field_value(row_dict, [
            '筛选日期', 'filter_date', '统计周期', 'statistics_period', '周期', '期间', '统计期间'
        ])
        
        # 饱和度直接读取数值，不需要转换为小数
        saturation_rate = get_field_value(row_dict, [
            'saturation_rate', '饱和度', '饱和率', '工作饱和度', '负荷率', '利用率'
        ])
        
        used_worktime = get_numeric_value(row_dict, [
            'used_worktime', '已用工时', '实际工时', '消耗工时', '使用工时', '投入工时'
        ])
        
        requirement_worktime = get_numeric_value(row_dict, [
            'requirement_worktime', '需求工时', '需求投入', '需求时间', '开发工时'
        ])
        
        ticket_worktime = get_numeric_value(row_dict, [
            'ticket_worktime', '工单工时', '工单时间', '支持工时', '维护工时'
        ])
        
        bug_worktime = get_numeric_value(row_dict, [
            'bug_worktime', 'bug工时', 'Bug工时', '缺陷工时', '修复工时', '问题工时'
        ])
        
        # 使用Excel中的筛选日期字段作为统计周期，如果没有则使用当前月份
        if statistics_period_field:
            period_str = str(statistics_period_field).strip()
            # 处理日期范围格式，如 "2025-09-01/2025-09-30" -> "2025-09"
            if '/' in period_str:
                # 取第一个日期的年月部分
                start_date = period_str.split('/')[0].strip()
                if len(start_date) >= 7:  # 至少包含 YYYY-MM
                    statistics_period = start_date[:7]  # 取前7位 YYYY-MM
                else:
                    statistics_period = period_str
            elif len(period_str) >= 7 and '-' in period_str:
                # 如果是完整日期格式 "2025-09-01"，取年月部分
                statistics_period = period_str[:7]
            else:
                # 其他格式直接使用
                statistics_period = period_str
        else:
            statistics_period = datetime.now().strftime('%Y-%m')  # 默认统计周期
        
        # 如果没有找到姓名，跳过这行
        if not name:
            continue
        
        # 查询该成员在项目工时明细表中的角色信息
        cursor.execute('''
            SELECT DISTINCT person_role 
            FROM project_worktime_details 
            WHERE person_name = ? AND person_role IS NOT NULL AND person_role != ''
            ORDER BY upload_time DESC
            LIMIT 1
        ''', (name,))
        
        role_result = cursor.fetchone()
        role = role_result[0] if role_result else None
        
        # 处理饱和度数值 - 如果是小数则转换为百分比，如果已经是百分比则直接使用
        if saturation_rate:
            try:
                saturation_value = float(saturation_rate)
                # 如果数值小于等于1，认为是小数形式，需要转换为百分比
                if saturation_value <= 1:
                    saturation_value = saturation_value * 100
                saturation_rate = saturation_value
            except (ValueError, TypeError):
                saturation_rate = 0
        else:
            saturation_rate = 0

        # 使用 INSERT OR REPLACE 来处理重复数据
        cursor.execute('''
            INSERT OR REPLACE INTO member_worktime_data 
            (name, statistics_period, saturation_rate, used_worktime, requirement_worktime, 
             ticket_worktime, bug_worktime, role)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?)
        ''', (
            name,
            statistics_period,
            saturation_rate,
            used_worktime or 0,
            requirement_worktime or 0,
            ticket_worktime or 0,
            bug_worktime or 0,
            role
        ))
        count += 1
    
    return count

if __name__ == '__main__':
    init_db()
    app.run(debug=True)